﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Configuration;
using System.Diagnostics;
using System.Data;

namespace ADO.NET.Samples.Common
{
    /// <summary>
    /// Provide data which is retrieved from database
    /// </summary>
    public class DBCommon
    {
        /// <summary>
        /// Return dataset which contains all the article and user data
        /// </summary>
        /// <returns></returns>
        public static DataSet GetArticleAndUserData()
        {
            DataSet ds = new DataSet();
            SqlCeConnection conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["ADO.NET.Samples.Properties.Settings.DBConnectionString"].ConnectionString);
            try
            {
                
                var articleAdpater = new SqlCeDataAdapter();
                var selectCommand = conn.CreateCommand();
                selectCommand.CommandText = "select * from Article";
                articleAdpater.SelectCommand = selectCommand;
                conn.Open();
                articleAdpater.Fill(ds, "Article");
                selectCommand.CommandText = "select * from [User]";
                var userAdpater = new SqlCeDataAdapter();
                userAdpater.SelectCommand = selectCommand;
                userAdpater.Fill(ds, "User");

            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }

        /// <summary>
        /// Return all the categories
        /// </summary>
        /// <returns></returns>
        public static List<ArticleCategory> GetAllCategories()
        {
            List<ArticleCategory> categories = new List<ArticleCategory>();
            SqlCeConnection conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["ADO.NET.Samples.Properties.Settings.DBConnectionString"].ConnectionString);
            try
            {
                var command = new SqlCeCommand();
                command.Connection = conn;
                command.CommandText = "select CategoryID,CategoryName from ArticleCategory";
                conn.Open();
                var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                var i = reader.GetOrdinal("CategoryID");
                var j = reader.GetOrdinal("CategoryName");
                while (reader.Read())
                {
                    var cate = new ArticleCategory
                    {
                        CategoryID = reader.GetInt32(i),
                        CategoryName = reader.GetString(j)
                    };
                    categories.Add(cate);
                }
                reader.Close();

            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return categories;
        }
    }
}
